package org.example;


import org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.ArrayList;

public class JDBCTest {
    public static void main(String[] args) throws Exception {
        //注册mysql驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取链接
        String url = "jdbc:mysql://localhost:3306/jdbc_bd2";
        Connection conn = DriverManager.getConnection(url, "root", "1234");
        String sql = "insert into user values(null,'hihi','123456','admin',20)";
        Statement stat = conn.createStatement();
        int count = stat.executeUpdate(sql);
        System.out.println("返回结果" + count);
        stat.close();
        conn.close();
    }

    @Test
    public void test1() throws Exception {
        //注册mysql驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取链接
        String url = "jdbc:mysql://localhost:3306/jdbc_bd2";
        Connection conn = DriverManager.getConnection(url, "root", "1234");
        //定义sql
        String sql = "select * from user";
        //获取执行sql对象
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery(sql);
        ArrayList<User> list = new ArrayList<>();
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            //打印获取的各种数据
            System.out.println(id + "--" + username + "--" + password + "--" + name + "--" + age);
            User user = new User(id, username, password, name, age);
            list.add(user);
        }
        System.out.println(list);
        rs.close();
        stat.close();
        conn.close();

    }

    @Test
    public void test2() throws Exception {
        //注册mysql驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取链接
        String url = "jdbc:mysql://localhost:3306/jdbc_bd2";
        Connection conn = DriverManager.getConnection(url, "root", "1234");
        //定义sql
        String sql = "select * from user where id>=?";
        //获取执行sql对象
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,3);
        ResultSet rs = ps.executeQuery(sql);
        ArrayList<User> list = new ArrayList<>();
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            //打印获取的各种数据
            System.out.println(id + "--" + username + "--" + password + "--" + name + "--" + age);
            User user = new User(id, username, password, name, age);
            list.add(user);
        }
        System.out.println(list);
        rs.close();
        ps.close();
        conn.close();

    }
}
